package com.elite.groovy.callback


import com.elite.groovy.util.EliteBean

import com.wisdge.dataservice.Result
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * Rick
 * @Title: 各坐席接听分析报表
 *
 */

public class apiDNSummaryData implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiDNSummaryData.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
 

        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        
        String dateSQL='''select date_format(FREEDATE,'%Y-%m-%d') as FREEDATE from C_FREEDATE where FREEDATE=date_format(now(),'%Y-%m-%d 00:00:00') and DATETYPE='000010' ''';
        List datelist= jt.queryForList(dateSQL);
        if(datelist.size()>0){
            String FREEDATE = datelist.get(0).get("FREEDATE");
            log.debug(FREEDATE+"各坐席接听汇总数据更新开始！")
            SummaryMain(eliteBean,FREEDATE);
        }

    }


    public static String SummaryMain(EliteBean eliteBean,date) {


        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        
        //检查推送数据
        String SummarySQL=''' REPLACE INTO summary_callib(summary_date,DN,DNName2,uniqueIdcount,qnobjtcount,qnoycjtcount,pdfqcount,pdsbcount,zlwjtcount,avgjxsc)
                                select 
                                        ?,
                                        p.DN,
                                        P.DNName2,
                                        sum(ifnull(a.uniqueIdcount,0)) as uniqueIdcount ,
                                        sum(ifnull(b.qnobjtcount,0)) as qnobjtcount,
                                        sum(ifnull(c.qnoycjtcount,0)) as qnoycjtcount,
                                        sum(ifnull(d.pdfqcount,0)) as pdfqcount,
                                        sum(ifnull(d.pdsbcount,0)) as pdsbcount,
                                        sum(ifnull(d.zlwjtcount,0)) as zlwjtcount,
                                        ifnull(e.avgjxsc,0) as avgjxsc
                                        from dn_list P 
                                        LEFT JOIN
                                        (
                                        select dl.DN,dl.DNName2,count(DISTINCT clci.uniqueId) as uniqueIdcount
                                            from clink_list_cdr_ibs clci
                                            LEFT JOIN (
                                                    select  a.* 
                                                from clink_describe_cdr_ib_ivr a
                                                LEFT JOIN (
                                                select cdcii.uniqueId,min(cdcii.routerStartTime) as mixrouterStartTime,min(cdcii.startTime) as mixstartTime
                                                 from clink_describe_cdr_ib_ivr cdcii
                                                where 1=1
                                                and cdcii.action='队列' 
                                                AND cdcii.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                                AND cdcii.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                                GROUP BY cdcii.uniqueId)
                                                t on a.uniqueId=t.uniqueId and  a.routerStartTime=t.mixrouterStartTime and a.startTime=t.mixstartTime
                                                where 1=1
                                                and  t.uniqueId is not null
                                                and  a.action='队列' 
                                                AND a.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                              AND a.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            ) cdcii on  cdcii.uniqueId=clci.uniqueId
                                            LEFT JOIN dn_list dll on cdcii.pathName=dll.IVR_path
                                            LEFT JOIN dn_list dl on FIND_IN_SET(dll.DN,dl.PosiTionDN)
                                            where 1=1 
                                            and clci.qno != ''
                                            and dll.DN is not null
                                            and dl.DN in (select dn from dn_list where is_reportshow = 1) 
                                            and cdcii.action='队列' 
                                          And Clci.Is_Ignore_Flag = 0
                                            AND clci.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND clci.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                             group by dl.DN,dl.DNName2
                                        ) a  on p.DN=a.DN and P.DN in (select dn from dn_list where is_reportshow = 1) 
                                        LEFT JOIN 
                                        (
                                         select dl.DN,dl.DNName2,	count(DISTINCT uniqueId) as qnobjtcount
                                            from staff s
                                            LEFT JOIN clink_queueagent_list cql on cql.cno=s.agent_id
                                            LEFT JOIN dn_list dl on Find_In_Set(cql.qno,dl.PosiTionDN)  
                                            LEFT JOIN (
                                                select  b.*,dl.DN,a.pathName
                                            from clink_describe_cdr_ib_ivr a
                                            LEFT JOIN clink_list_cdr_ibs b on a.uniqueId=b.uniqueId 
                                            LEFT JOIN (
                                            select cdcii.uniqueId,min(cdcii.routerStartTime) as mixrouterStartTime,min(cdcii.startTime) as mixstartTime
                                             from clink_describe_cdr_ib_ivr cdcii
                                            where 1=1
                                            and cdcii.action='队列' 
                                            AND cdcii.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND cdcii.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            GROUP BY cdcii.uniqueId
                                            )
                                            t on a.uniqueId=t.uniqueId and  a.routerStartTime=t.mixrouterStartTime and a.startTime=t.mixstartTime
                                            LEFT JOIN dn_list dl on a.pathName=dl.IVR_path
                                            where 1=1
                                            and  t.uniqueId is not null
                                            and  a.action='队列' 
                                            AND a.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND a.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            ) clci 
                                            on  clci.cno=s.agent_id and cql.qno = clci.DN
                                            where 1=1 
                                            and cql.qno != '' and cql.cno != '' 
                                            and clci.DN != '' and clci.DN != '' 
                                            and clci.status = '座席接听'
                                            and dl.DN is not null
                                            and dl.DN in (select dn from dn_list where is_reportshow = 1) 
                                            AND clci.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND clci.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                         group by dl.DN,dl.DNName2
                                        ) b 
                                        on p.DN=b.DN
                                        LEFT JOIN
                                        (
                                            select dd.DN,dd.DNName2,count(DISTINCT uniqueId) as qnoycjtcount from (
                                            select clci.uniqueId,clci.DN as qno, clci.cno ,GROUP_CONCAT(DISTINCT cql.qno)  as 实际接听队列
                                            from  (
                                                select  b.*,dl.DN,a.pathName
                                            from clink_describe_cdr_ib_ivr a
                                            LEFT JOIN clink_list_cdr_ibs b on a.uniqueId=b.uniqueId 
                                            LEFT JOIN (
                                            select cdcii.uniqueId,min(cdcii.routerStartTime) as mixrouterStartTime,min(cdcii.startTime) as mixstartTime
                                             from clink_describe_cdr_ib_ivr cdcii
                                            where 1=1
                                            and cdcii.action='队列' 
                                            AND cdcii.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND cdcii.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            GROUP BY cdcii.uniqueId
                                            )
                                            t on a.uniqueId=t.uniqueId and  a.routerStartTime=t.mixrouterStartTime and a.startTime=t.mixstartTime
                                            LEFT JOIN dn_list dl on a.pathName=dl.IVR_path
                                            where 1=1
                                            and  t.uniqueId is not null
                                            and  a.action='队列' 
                                            AND a.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND a.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            ) clci 
                                            left join clink_queueagent_list cql on cql.cno=clci.cno
                                            left join staff r1 on clci.cno = r1.agent_id
                                            left join staffskillgroup r2 on r1.staff_id = r2.staff_id
                                            left join skillgroup r3 on r2.SKILLGROUP_ID = r3.SKILLGROUP_ID
                                            left join dn_list r4 on cql.qno = r4.dn and Find_In_Set(cql.qno,r3.DN_IDS) 
                                            where 1=1 
                                            and clci.status = '座席接听' 
                                            AND clci.starttime >= Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND clci.starttime <= Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            and r4.dn is not null
                                            group by clci.uniqueId,clci.DN, clci.cno
                                            ) r1 
                                            LEFT JOIN dn_list dl on Find_In_Set(dl.dn,r1.实际接听队列)  
                                            LEFT JOIN dn_list dd on Find_In_Set(dl.dn,dd.PosiTionDN)  
                                            where 1=1 and dd.DN in (select dn from dn_list where is_reportshow = 1)  and FIND_IN_SET(r1.qno,r1.实际接听队列)=0
                                            GROUP BY dd.DN,dd.DNName2
                                        ) c on p.DN=c.DN
                                        LEFT JOIN 
                                        (
                                        select case when dl.DN='10009' then '10001' else dl.DN end as DN,dl.DNName2,
                                            Sum(If(clci.clientRingingTime='' and clci.mark = 11 and clci.Is_Abandon_Fail=1, 1, 0)) As pdfqcount, -- 呼损 排队放弃
                                            Sum(If(clci.clientRingingTime='' and clci.mark != 11 and clci.Is_TimeOut_Fail=1, 1, 0)) As pdsbcount, -- 呼损 排队超时 
                                            Sum(If(clci.clientRingingTime != '' and clci.status = '座席未接听' and clci.is_Call_Fail=1, 1, 0)) As zlwjtcount -- 响铃 未接通
                                            from (
                                                select DISTINCT b.uniqueId,b.clientRingingTime,b.status,b.mark,dl.DN,a.pathName,b.Is_Abandon_Fail,b.Is_TimeOut_Fail,b.is_Call_Fail
                                            from clink_describe_cdr_ib_ivr a
                                            LEFT JOIN clink_list_cdr_ibs b on a.uniqueId=b.uniqueId 
                                            LEFT JOIN (
                                            select cdcii.uniqueId,min(cdcii.routerStartTime) as mixrouterStartTime,min(cdcii.startTime) as mixstartTime
                                             from clink_describe_cdr_ib_ivr cdcii
                                            where 1=1
                                            and cdcii.action='队列' 
                                            AND cdcii.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND cdcii.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            GROUP BY cdcii.uniqueId
                                            )
                                            t on a.uniqueId=t.uniqueId and  a.routerStartTime=t.mixrouterStartTime and a.startTime=t.mixstartTime
                                            LEFT JOIN dn_list dl on a.pathName=dl.IVR_path
                                            where 1=1
                                            and  t.uniqueId is not null
                                            and  a.action='队列' 
                                            and  b.qno <>''
                                            AND a.starttime >=  Unix_Timestamp(date_format(?,'%Y-%m-%d 00:00:00'))
                                            AND a.starttime <=  Unix_Timestamp(date_format(?,'%Y-%m-%d 23:59:59'))
                                            ) clci
                                            LEFT JOIN dn_list dl on Find_In_Set(clci.DN,dl.PosiTionDN)  
                                            where 1=1 
                                            and clci.DN != ''	and clci.DN is not null
                                            and dl.DN is not null
                                            and dl.DN in (select dn from dn_list where is_reportshow = 1 union select '10009') 
                                            group by  1,2
                                        ) d on p.DN=d.DN
                                        LEFT JOIN 
                                        (select c.DN,c.DNName2,Round(sum(k.jxsc)*1.0/3600/(
                                            select count(*) as gzr from C_FREEDATE t  where 1=1 
                                            and t.DATETYPE='000010'  
                                                and DATE_FORMAT(t.FREEDATE,'%Y-%m-%d')>= date_format(?,'%Y-%m-%d')
                                            and DATE_FORMAT(t.FREEDATE,'%Y-%m-%d')<= date_format(?,'%Y-%m-%d')
                                        ),2) as  avgjxsc
                                         from (
                                                select b.cno,
                                                TIMESTAMPDIFF( SECOND, 
                                            case 
                                                when b.startTime<=a.work_starttime then a.work_starttime 
                                                when b.startTime>=a.work_starttime and b.startTime<=a.noon_starttime then b.startTime
                                                when b.startTime>=a.work_starttime and b.startTime>=a.noon_starttime and b.startTime<=a.noon_endtime then a.noon_endtime
                                                when b.startTime>=a.work_starttime and b.startTime>=a.noon_starttime and b.startTime>=a.noon_endtime and b.startTime<=a.work_endtime then b.startTime
                                                when b.startTime>=a.work_starttime and b.startTime>=a.noon_starttime and b.startTime>=a.noon_endtime and b.startTime>=a.work_endtime then a.work_endtime
                                                else 0 end,
                                            case 
                                                when b.endTime<=a.work_starttime then a.work_starttime 
                                                when b.endTime>=a.work_starttime and b.endTime<=a.noon_starttime then b.endTime
                                                when b.endTime>=a.work_starttime and b.endTime>=a.noon_starttime and b.endTime<=a.noon_endtime then a.noon_endtime
                                                when b.endTime>=a.work_starttime and b.endTime>=a.noon_starttime and b.endTime>=a.noon_endtime and b.endTime<=a.work_endtime then b.endTime
                                                when b.endTime>=a.work_starttime and b.endTime>=a.noon_starttime and b.endTime>=a.noon_endtime and b.endTime>=a.work_endtime then a.work_endtime
                                                else 0 end ) as jxsc
                                                from (
                                                    select 
                                                    DATE_FORMAT(t.FREEDATE,'%Y-%m-%d') as FREEDATE,
                                                    concat(DATE_FORMAT(t.FREEDATE,'%Y-%m-%d '),t.work_starttime) as work_starttime ,
                                                    concat(DATE_FORMAT(t.FREEDATE,'%Y-%m-%d '),t.work_endtime) as work_endtime,
                                                    concat(DATE_FORMAT(t.FREEDATE,'%Y-%m-%d '),t.noon_starttime) as noon_starttime,
                                                    concat(DATE_FORMAT(t.FREEDATE,'%Y-%m-%d '),t.noon_endtime) as noon_endtime
                                                from C_FREEDATE t  
                                                where 1=1 
                                                    and t.DATETYPE='000010'  
                                                        and DATE_FORMAT(t.FREEDATE,'%Y-%m-%d')>= date_format(?,'%Y-%m-%d')
                                                    and DATE_FORMAT(t.FREEDATE,'%Y-%m-%d')<= date_format(?,'%Y-%m-%d')
                                                ) a
                                                LEFT JOIN 
                                                (
                                                select 		
                                                DATE_FORMAT(cllad.startTime,'%Y-%m-%d') as FREEDATE,
                                                cllad.cno,
                                                cllad.startTime,
                                                cllad.endTime,
                                                cllad.duration  
                                                from clink_list_log_agent_duration cllad 
                                             WHERE 1=1 
                                             AND cllad.startTime >= date_format(?,'%Y-%m-%d 00:00:00')	
                                             AND cllad.startTime <= date_format(?,'%Y-%m-%d 23:59:59') 
                                             AND cllad.stateDesc in ('通话','空闲','呼叫中')
                                                ) b on a.FREEDATE=b.FREEDATE  
                                            ) k
                                         LEFT JOIN 
                                            (
                                                select case r3.SKILLGROUPNAME
                                            when '投诉与建议' then '10001'
                                            when '技术咨询' then '20001'
                                            when '销售咨询' then '30001'
                                            when '专卖店开店' then '40001'
                                            when '巧匠热线' then '50001'
                                            when '扫码红包' then '50009' end dn,
                                            case r3.SKILLGROUPNAME
                                            when '投诉与建议' then '投诉及建议'
                                            when '技术咨询' then '技术咨询'
                                            when '销售咨询' then '销售咨询'
                                            when '专卖店开店' then '专卖店及开店'
                                            when '巧匠热线' then '巧匠热线'
                                            when '扫码红包' then '扫码红包' end DNName2 
                                            ,r1.AGENT_ID as cno
                                            from staff r1 
                                            left join staffskillgroup r2 on r1.staff_id = r2.staff_id
                                            left join skillgroup r3 on r2.SKILLGROUP_ID = r3.SKILLGROUP_ID	
                                                where r3.SKILLGROUPNAME is  not null 
                                            group by 1,2,3
                                            ) c  on k.cno=c.cno
                                                where 1=1
                                             group by c.DN,c.DNName2
                                        ) e on p.DN=e.DN
                                        where  P.DN in (select dn from dn_list where is_reportshow = 1) 
                                     group by ?,p.DN,p.DNName2,avgjxsc '''

        jt.update(SummarySQL,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date);

        log.debug(date.toString()+"各坐席接听汇总数据更新完成！")
    }

}




